using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Data.OleDb;

namespace Login
{
    public partial class FormSCEn : FormBase
    {
        string strConnString = "Provider = SQLOLEDB; Data Source =.; Integrated Security = SSPI; Initial Catalog = STUDB";

        public FormSCEn(ToolStripMenuItem tsmi) : base(tsmi)
        {
            InitializeComponent();
        }        

        private void FormSC_Load(object sender, EventArgs e)
        {
            ShowCopyBtn = false;
            ShowDelBtn = false;
            ShowNavigator = true;
            DbOp dbOp = new DbOp(strConnString);
            string sql = @"select *, case when sc.CNO is not null then 1 else null end CHECKED from course c left join sc on c.cno = sc.CNO and sno = ?";

            DataTable dt = dbOp.GetDataTable("select * from student", null);

            dt = dbOp.GetDataTable(sql, UserName);
            DataView dv = dt.DefaultView;
            BindingSource bs = new BindingSource();
            bs.DataSource = dv;
            gridView.AutoGenerateColumns = false;
            gridView.DataSource = bs;
            Navigator.BindingSource = bs;
        }

        protected override void Save()
        {
            //1）初始已选课——》学生退选——》再次选课（无须处理）
            //2）初始未选课——》学生选课——》退课 （无须处理）
            //3）初始未选课——》学生选课（插入选课记录到SC）
            //4）初始已选课——》学生退选（删除SC选课记录）
            try
            {
                string sql = null;
                DbOp dbOp = new DbOp(strConnString);

                // 验证当前登录用户的学号是否在学生表中存在
                DataTable dtStudent = dbOp.GetDataTable("select * from student where sno = ?", UserName);
                if (dtStudent.Rows.Count == 0)
                {
                    MessageBox.Show("当前用户的学号在学生表中不存在，无法进行选课操作！");
                    return;
                }

                DataTable dtSC = GetDataTableByGrid(gridView);
                BindingSource bs = GetBindingSourceByGrid(gridView);
                if (dtSC != null)
                {
                    gridView.EndEdit();
                    if (bs != null)
                        bs.EndEdit();
                    DataTable dtChanged = dtSC.GetChanges();
                    if (dtChanged != null)
                    {
                        foreach (DataRow dr in dtChanged.Rows)
                        {
                            if (dr["checked", DataRowVersion.Original] == DBNull.Value &&
                                dr["checked", DataRowVersion.Current].Equals(1))
                            {
                                //3）初始未选课(原始值checked为空)——》学生选课（插入选课记录到SC）
                                sql = "insert into sc (sno, cno) values (?, ?)";
                            }
                            else if(dr["checked", DataRowVersion.Original].Equals(1) &&
                                dr["checked", DataRowVersion.Current] == DBNull.Value)
                            {
                                //4）初始已选课——》学生退选（删除SC选课记录）
                                sql = "delete from sc where sno = ? and cno = ?";
                            }
                            dbOp.ExecuteNonQuery(sql, UserName, dr["cno"]);
                        }
                        //数据保存成功后，数据必须处于一致状态
                        dtSC.AcceptChanges();
                        MessageBox.Show("恭喜您！选课成功！");
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
            }
        }
    }
}
